import sqlite3
import sys

# 数据库路径
DB_PATH = 'C:/Users/weiye/AppData/Roaming/military-engineering-management/military_engineering.db'

# 需要检查的字段列表
required_fields = [
    # 基本信息字段
    'id', 'project_number', 'project_code', 'name', 'description', 'location',
    'status', 'sub_status', 'progress', 'phase_status', 'task_document_no',
    'planning_type', 'construction_type', 'total_investment', 'direct_cost',
    
    # 任务书相关日期
    'task_issue_date', 'required_completion_date', 'extended_completion_date',
    
    # 设计阶段日期
    'scheme_design_confirm_date', 'preliminary_design_confirm_date', 'construction_drawing_review_date',
    'bill_compilation_date', 'bill_audit_date', 'construction_drawing_confirm_date',
    
    # 发包阶段日期
    'bidding_announcement_date', 'enterprise_registration_date', 'bid_opening_date',
    'bid_award_date', 'bid_notice_date', 'contract_signing_date', 'direct_commission_date',
    
    # 施工阶段日期
    'quality_supervision_registration_date', 'construction_permit_date', 'construction_start_order_date',
    'preliminary_acceptance_date', 'archive_acceptance_date', 'completion_acceptance_date',
    
    # 竣工阶段日期
    'entity_handover_date', 'archive_handover_date', 'completion_filing_date',
    'settlement_completion_date', 'final_account_completion_date',
    
    # 合同信息
    'bidding_mode', 'contract_start_date', 'contract_end_date', 'contract_duration', 'contract_amount',
    
    # 参建单位信息
    'survey_design_unit', 'cost_compilation_unit', 'cost_audit_unit',
    'construction_unit', 'supervision_unit', 'using_unit', 'using_unit_name',
    
    # 联系信息
    'field_contact', 'contact_phone',
    
    # 进度信息
    'current_week_work', 'next_week_plan', 'visual_progress', 'issues', 'solutions', 'payment_progress',
    
    # 系统字段
    'detail_page_start_time', 'created_at', 'updated_at', 'notes'
]

def main():
    try:
        # 连接数据库
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        
        # 获取projects表的当前字段
        cursor.execute('PRAGMA table_info(projects)')
        columns = cursor.fetchall()
        
        # 提取字段名
        existing_fields = [col[1] for col in columns]
        print(f"数据库中已存在的字段 ({len(existing_fields)}):")
        for field in existing_fields:
            print(f"  - {field}")
        
        # 找出缺失的字段
        missing_fields = [field for field in required_fields if field not in existing_fields]
        print(f"\n缺失的字段 ({len(missing_fields)}):")
        for field in missing_fields:
            print(f"  - {field}")
        
        # 自动执行ALTER TABLE语句
        if missing_fields:
            print("\n执行以下SQL语句来添加缺失的字段:")
            for field in missing_fields:
                try:
                    sql = f"ALTER TABLE projects ADD COLUMN {field} TEXT;"
                    print(sql)
                    cursor.execute(sql)
                    print(f"已添加字段: {field}")
                except sqlite3.Error as e:
                    print(f"添加字段 {field} 时出错: {e}")
            
            # 提交更改
            conn.commit()
            print("所有更改已提交到数据库")
        else:
            print("\n数据库表结构已完整，不需要添加字段")
        
    except sqlite3.Error as e:
        print(f"数据库错误: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    main() 